package com.xindacloud.sms.console.system.service.impl;


import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xindacloud.sms.console.constant.RoleLevel;
import com.xindacloud.sms.console.constant.SessionKeys;
import com.xindacloud.sms.console.result.Result;
import com.xindacloud.sms.console.result.ResultGenerator;
import com.xindacloud.sms.console.system.dto.*;
import com.xindacloud.sms.console.system.entity.SmsAgent;
import com.xindacloud.sms.console.system.entity.SmsRole;
import com.xindacloud.sms.console.system.entity.SmsUserAllowOperator;
import com.xindacloud.sms.console.system.entity.SmsUserRole;
import com.xindacloud.sms.console.system.mapper.SmsAgentMapper;
import com.xindacloud.sms.console.system.mapper.SmsRoleMapper;
import com.xindacloud.sms.console.system.mapper.SmsUserAllowOperatorMapper;
import com.xindacloud.sms.console.system.mapper.SmsUserRoleMapper;
import com.xindacloud.sms.console.system.service.CommonServiceImpl;
import com.xindacloud.sms.console.system.service.ISmsUserAllowOperatorService;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 * 授权给用户的短信通道 服务实现类
 * </p>
 *
 * @author czc
 * @since 2019-10-08
 */
@Service
public class SmsUserAllowOperatorServiceImpl extends ServiceImpl<SmsUserAllowOperatorMapper, SmsUserAllowOperator> implements ISmsUserAllowOperatorService {

    @Autowired
    private CommonServiceImpl commonService;


    @Autowired
    private SmsUserAllowOperatorMapper smsUserAllowOperatorMapper;

    @Override
    public boolean updateUserAllowOperator(Integer userId, List<String> operatorIds, Boolean status, Integer operatorUserId) {
        //Integer roleLevel = getRole(operatorUserId);
       /* if (roleLevel != null && roleLevel.equals(1)) {
            QueryWrapper<SmsUserAllowOperator> wrapper = new QueryWrapper<>();
            wrapper.eq("user_id", userId);
            wrapper.in("operator_id", operatorIds);
            List<SmsUserAllowOperator> uaList = this.list(wrapper);
            List<String> allowOperatorIds = new ArrayList<>();
            for (SmsUserAllowOperator uao : uaList) {
                SmsUserAllowOperator userAllowOperator = this.getById(uao.getAllowOperatorId());
                QueryWrapper<SmsUserAllowOperator> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("user_id", userAllowOperator.getUserId());
                queryWrapper.eq("country_code", userAllowOperator.getCountryCode());
                queryWrapper.eq("network_name", userAllowOperator.getNetworkName());
                List<SmsUserAllowOperator> qwList = this.list(queryWrapper);
                for (SmsUserAllowOperator suo : qwList) {
                    allowOperatorIds.add(suo.getAllowOperatorId());
                }
            }
            return lambdaUpdate()
                    .set(SmsUserAllowOperator::getStatus, status)
                    .in(SmsUserAllowOperator::getAllowOperatorId, allowOperatorIds)
                    .update();
        }*/

        return lambdaUpdate()
                .set(SmsUserAllowOperator::getStatus, status)
                .in(SmsUserAllowOperator::getOperatorId, operatorIds)
                .eq(SmsUserAllowOperator::getUserId, userId)
                .update();
    }


    public Integer getRole(Integer userId) {
        QueryWrapper<SmsUserRole> userRoleWrapper = new QueryWrapper<>();
        userRoleWrapper.eq("user_id", userId);
        List<SmsUserRole> surList = smsUserRoleMapper.selectList(userRoleWrapper);
        Set<Integer> iSet = new HashSet<>();
        for (SmsUserRole smsUserRole : surList) {
            QueryWrapper<SmsRole> roleWrapper = new QueryWrapper<>();
            roleWrapper.eq("role_id", smsUserRole.getRoleId());
            List<SmsRole> smsRoleList = smsRoleMapper.selectList(roleWrapper);
            for (SmsRole smsRole : smsRoleList) {
                iSet.add(smsRole.getRolePrivLevel());
            }
        }
        Integer roleLevel = 0;
        if (iSet != null && iSet.size() > 0) {
            roleLevel = Collections.max(iSet);
        }
        return roleLevel;
    }


    @Override
    public boolean saveSmsUserAllowOperatorBatch(List<SmsUserAllowOperator> userAllowOperatorList) {
        return saveBatch(userAllowOperatorList);
    }

    @Override
    public boolean deleteUserAllowOperatorByUserId(Integer userId) {
        Wrapper<SmsUserAllowOperator> wrapper = new QueryWrapper<>(SmsUserAllowOperator.builder().userId(userId).build());
        return remove(wrapper);
    }

    @Override
    public boolean updateSmsUserAllowBatch(List<SmsUserAllowOperator> userAllowOperatorList) {
        return updateBatchById(userAllowOperatorList);
    }

    @Override
    public List<SmsUserAllowOperator> querySmsUserAllowOperatorList(List<String> operatorIds, Integer userId) {
        return lambdaQuery()
                .in(SmsUserAllowOperator::getOperatorId, operatorIds)
                .eq(SmsUserAllowOperator::getUserId, userId)
                .list();
    }

    @Override
    public Result importPriceExcel(MultipartFile file, String agentId, String agentGroup) {
        List<SmsUserAllowOperator> infoList = new ArrayList<>();

        try {
            final String originalFilename = file.getOriginalFilename();
            assert originalFilename != null;
            if (originalFilename.substring(originalFilename.lastIndexOf(".")).equalsIgnoreCase(".csv")) {
            	/*if(agentGroup.equalsIgnoreCase("nexmo")) {
            		infoList.addAll(readNexmoCSVFile(file.getInputStream(), agentId));
            	}else if(agentGroup.equalsIgnoreCase("sinch")) {
            		infoList.addAll(readSinchCSVFile(file.getInputStream(), agentId));
            	}else if(agentGroup.equalsIgnoreCase("cm")) {
            		infoList.addAll(readCmCSVFile(file.getInputStream(), agentId));
            	}else {
            		
            	}*/

            } else {
                if (agentGroup.equalsIgnoreCase("nexmo")) {
                    infoList.addAll(readNexmoExcel(file.getInputStream(), agentId));
                } else if (agentGroup.equalsIgnoreCase("sinch")) {
                    infoList.addAll(readSinchExcel(file.getInputStream(), agentId));
                } else if (agentGroup.equalsIgnoreCase("cm")) {
                    infoList.addAll(readCmExcel(file.getInputStream(), agentId));
                } else {

                }

            }
        } catch (IOException | InvalidFormatException e) {
            log.error(e.getMessage(), e);
        }
        if (this.saveBatch(infoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }
    }

    @Override
    public void downloadAllPrice(HttpServletRequest request, HttpServletResponse response, SmsUserAllowOperatorDTO smsUserAllowOperatorDTO) {
        try {
            // 查询用户角色
            //Integer roleLevel = getRole(smsUserAllowOperatorDTO.getOperatorUserId());
            //-----
            List<SmsUserAllowOperator> bgmExcelDownloads = null;
            /*if (roleLevel != null && roleLevel.equals(1)) {
                bgmExcelDownloads = smsUserAllowOperatorMapper.selectSalesPricing(smsUserAllowOperatorDTO);
            } else {*/
            QueryWrapper<SmsUserAllowOperator> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("user_id", smsUserAllowOperatorDTO.getUserId());
            bgmExcelDownloads = this.list(queryWrapper);

            //}

            //查询用户角色
            Integer userRoleLevel = commonService.fetchUserRoleLevel(smsUserAllowOperatorDTO.getUserId());

            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("获取excel表格数据");

            HSSFRow row = null;

			/*row = sheet.createRow(0);//创建第一个单元格
			row.setHeight((short) (26.25 * 20));
			row.createCell(0).setCellValue("用户信息列表");//为第一行单元格设值
*/
            /*为标题设计空间
             * firstRow从第1行开始
             * lastRow从第0行结束
             *
             *从第1个单元格开始
             * 从第3个单元格结束
             */
            //CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 1);
            //sheet.addMergedRegion(rowRegion);
            row = sheet.createRow(0);
            row.setHeight((short) (22.50 * 20));//设置行高
            row.createCell(0).setCellValue("operatorId(请勿修改)");//为第一个单元格设值
            row.createCell(1).setCellValue("代理商ID(请勿修改)");//为第二个单元格设值
            row.createCell(2).setCellValue("国家简码");//为第三个单元格设值
            row.createCell(3).setCellValue("国家名称");
            row.createCell(4).setCellValue("国家显示名称");
            row.createCell(5).setCellValue("网络");
            row.createCell(6).setCellValue("网络区域码");
            row.createCell(7).setCellValue("运营商名称");
            row.createCell(8).setCellValue("前缀");
            row.createCell(9).setCellValue("成本价");
            row.createCell(10).setCellValue("售价");
            row.createCell(11).setCellValue("默认价格");
            row.createCell(12).setCellValue("货币单位");
            row.createCell(13).setCellValue("type类型");
            row.createCell(14).setCellValue("mcc");
            row.createCell(15).setCellValue("mnc");
            row.createCell(16).setCellValue("userId(请勿修改)");
            row.createCell(17).setCellValue("allowOperatorId(请勿修改)");



            //遍历所获取的数据
            for (int i = 0; i < bgmExcelDownloads.size(); i++) {
                row = sheet.createRow(i + 1);
                SmsUserAllowOperator sp = bgmExcelDownloads.get(i);
                row.createCell(0).setCellValue(sp.getOperatorId());
                row.createCell(1).setCellValue(sp.getAgentId());
                row.createCell(2).setCellValue(sp.getCountryCode());
                row.createCell(3).setCellValue(sp.getCountryName());
                row.createCell(4).setCellValue(sp.getCountryDisplayName());
                row.createCell(5).setCellValue(sp.getNetworkCode());
                row.createCell(6).setCellValue(sp.getNetworkAliases());
                row.createCell(7).setCellValue(sp.getNetworkName());
                row.createCell(8).setCellValue(sp.getPrefix());
                if (userRoleLevel.equals(RoleLevel.ADMIN)) {
                    row.createCell(9).setCellValue(String.valueOf(sp.getCostPrice()));
                } else {
                    row.createCell(9).setCellValue("");
                }
                row.createCell(10).setCellValue(String.valueOf(sp.getPrice()));
                row.createCell(11).setCellValue(String.valueOf(sp.getDefaultPrice()));
                row.createCell(12).setCellValue(sp.getCurrency());
                row.createCell(13).setCellValue(sp.getType());
                row.createCell(14).setCellValue(sp.getMcc());
                row.createCell(15).setCellValue(sp.getMnc());
                row.createCell(16).setCellValue(sp.getUserId());
                row.createCell(17).setCellValue(sp.getAllowOperatorId());

            }
            sheet.setDefaultRowHeight((short) (16.5 * 20));
            //列宽自适应
            for (int i = 0; i <= 13; i++) {
                sheet.autoSizeColumn(i);
            }


            OutputStream os = response.getOutputStream();
            String ename = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
            String fileName = "excel_" + ename + ".xls";
            response.setHeader("Access-Control-Expose-Headers", SessionKeys.SMS_TOKEN + ",Content-Disposition");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Content-Type", "application/vnd.ms-excel");

            wb.write(os);
            response.flushBuffer();
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Result uploadAllPrice(MultipartFile file, Integer userId, Integer operatorUserId) {
        List<SmsUserAllowOperator> infoList = null;
        try {
            infoList = readSmsPriceExcel(file.getInputStream(), userId);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //查询销售角色
        //Integer roleLevel = getRole(operatorUserId);

        List<SmsUserAllowOperator> uaoList = new ArrayList<SmsUserAllowOperator>();

        for (SmsUserAllowOperator uao : infoList) {
            uaoList.add(uao);
        }
        if (this.saveOrUpdateBatch(uaoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }
    }

    private List<SmsUserAllowOperator> readSmsPriceExcel(InputStream inputStream, Integer userId) throws IOException, InvalidFormatException {
        Integer userRoleLevel = commonService.fetchUserRoleLevel(userId);

        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            SmsUserAllowOperator info = new SmsUserAllowOperator();
            info.setUserId(userId);
            if (Objects.nonNull(row.getCell(0))) {
                row.getCell(0).setCellType(CellType.STRING);
                info.setOperatorId(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                row.getCell(1).setCellType(CellType.STRING);
                info.setAgentId(row.getCell(1).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(2))) {
                row.getCell(2).setCellType(CellType.STRING);
                info.setCountryCode(row.getCell(2).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                info.setCountryName(row.getCell(3).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(4))) {
                row.getCell(4).setCellType(CellType.STRING);
                info.setCountryDisplayName(row.getCell(4).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(5))) {
                row.getCell(5).setCellType(CellType.STRING);
                info.setNetworkCode(row.getCell(5).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(6))) {
                row.getCell(6).setCellType(CellType.STRING);
                info.setNetworkAliases(row.getCell(6).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(7))) {
                row.getCell(7).setCellType(CellType.STRING);
                info.setNetworkName(row.getCell(7).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(8))) {
                row.getCell(8).setCellType(CellType.STRING);
                info.setPrefix(row.getCell(8).getStringCellValue());
            }

            if (userRoleLevel.equals(RoleLevel.ADMIN)) {
                if (Objects.nonNull(row.getCell(9))) {
                    row.getCell(9).setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(row.getCell(9).getStringCellValue())) {
                        info.setCostPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(9).getStringCellValue())));
                    }
                }
            }

            if (Objects.nonNull(row.getCell(10))) {
                row.getCell(10).setCellType(CellType.STRING);
                if (StringUtils.isNotEmpty(row.getCell(10).getStringCellValue())) {
                    info.setPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(10).getStringCellValue())));
                }
            }
            if (Objects.nonNull(row.getCell(11))) {
                row.getCell(11).setCellType(CellType.STRING);
                if (StringUtils.isNotEmpty(row.getCell(10).getStringCellValue())) {
                    info.setDefaultPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(11).getStringCellValue())));
                }
            }

            if (Objects.nonNull(row.getCell(12))) {
                row.getCell(12).setCellType(CellType.STRING);
                info.setCurrency(row.getCell(12).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(13))) {
                row.getCell(13).setCellType(CellType.STRING);
                info.setType(row.getCell(13).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(14))) {
                row.getCell(14).setCellType(CellType.STRING);
                info.setMcc(row.getCell(14).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(15))) {
                row.getCell(15).setCellType(CellType.STRING);
                info.setMnc(row.getCell(15).getStringCellValue());
            }
	           /* if (Objects.nonNull(row.getCell(16))) {
	            	row.getCell(16).setCellType(CellType.STRING);
	            	info.setUserId(Integer.valueOf(row.getCell(16).getStringCellValue()));
	            }*/
            if (Objects.nonNull(row.getCell(17))) {
                row.getCell(17).setCellType(CellType.STRING);
                info.setAllowOperatorId(row.getCell(17).getStringCellValue());
            }
            if (StringUtils.isEmpty(info.getCountryCode())) {
                continue;
            }
            smsPricingList.add(info);
        }
        return smsPricingList;
    }


    @Override
    public Result updateBatchPrice(SmsUpdatePricingDTO smsUpdatePricingDTO) {
        List<SmsUserAllowOperator> infoList = new ArrayList<>();
        List<String> ids = smsUpdatePricingDTO.getOperatorIds();
        if (smsUpdatePricingDTO.getSymbol().equals("+")) {
            for (String operatorId : ids) {
                SmsUserAllowOperator info = this.getById(operatorId);
                BigDecimal costPrice = info.getCostPrice();
                BigDecimal defaultPrice = info.getDefaultPrice();
                BigDecimal salesPrice = defaultPrice.add(smsUpdatePricingDTO.getCountValue());

                if (salesPrice.divide(defaultPrice, 8, RoundingMode.HALF_DOWN).compareTo(new BigDecimal(info.getDefaultAndSales())) == -1) {
                    return ResultGenerator.genClientFailResult("售价不能低于默认价的" + info.getDefaultAndSales() + "倍数!需要此价格，请联系管理员申请！");
                }
                info.setPrice(salesPrice);
                infoList.add(info);
            }
        } else {
            for (String operatorId : ids) {
                SmsUserAllowOperator info = this.getById(operatorId);
                BigDecimal costPrice = info.getCostPrice();
                BigDecimal defaultPrice = info.getDefaultPrice();
                BigDecimal salesPrice = defaultPrice.multiply(smsUpdatePricingDTO.getCountValue());

                if (salesPrice.divide(defaultPrice, 8, RoundingMode.HALF_DOWN).compareTo(new BigDecimal(info.getDefaultAndSales())) == -1) {
                    return ResultGenerator.genClientFailResult("售价不能低于默认价的" + info.getDefaultAndSales() + "倍数!需要此价格，请联系管理员申请！");
                }
                info.setPrice(salesPrice);
                infoList.add(info);
            }
        }
        //Integer roleLevel = getRole(smsUpdatePricingDTO.getOperatorUserId());
        List<SmsUserAllowOperator> uaList = new ArrayList<>();

        for (SmsUserAllowOperator suao : infoList) {
            uaList.add(suao);
        }
        //}

        if (this.updateBatchById(uaList)) {
            return ResultGenerator.genSuccessResult("批量修改价格成功!");
        } else {
            return ResultGenerator.genClientFailResult("批量修改价格失败!");
        }
    }

    private List<SmsUserAllowOperator> readNexmoExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsUserAllowOperator info = new SmsUserAllowOperator();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryCode(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                info.setCountryName(row.getCell(1).getStringCellValue());
                info.setCountryDisplayName(row.getCell(1).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(2))) {
                info.setNetworkCode(row.getCell(2).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(3))) {
                info.setNetworkAliases(row.getCell(3).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(4))) {
                info.setNetworkName(row.getCell(4).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(5))) {
                info.setPrefix(row.getCell(5).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(6))) {
                final BigDecimal price = BigDecimal.valueOf(row.getCell(6).getNumericCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            if (Objects.nonNull(row.getCell(0)) && Objects.nonNull(row.getCell(5))) {
                info.setCurrency("EUR");
                info.setAgentId(agentId);
                info.setType("mobile");
                smsPricingList.add(info);
            }
        }
        return smsPricingList;
    }

    private List<SmsUserAllowOperator> readSinchExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsUserAllowOperator info = new SmsUserAllowOperator();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryCode(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                final BigDecimal price = BigDecimal.valueOf(row.getCell(1).getNumericCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            if (Objects.nonNull(row.getCell(2))) {
                info.setCountryName(row.getCell(2).getStringCellValue());
                info.setCountryDisplayName(row.getCell(2).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                info.setPrefix(row.getCell(3).getStringCellValue());
            }
            info.setCurrency("EUR");
            info.setAgentId(agentId);
            info.setType("mobile");
            smsPricingList.add(info);
        }
        return smsPricingList;
    }

    private List<SmsUserAllowOperator> readCmExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsUserAllowOperator info = new SmsUserAllowOperator();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryName(row.getCell(0).getStringCellValue());
                info.setCountryDisplayName(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                info.setCountryCode(row.getCell(1).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                String str = row.getCell(3).getStringCellValue();
                StringBuffer sb = new StringBuffer();
                String prefix = "";
                String[] strs = str.split(",");
                if (strs.length > 0) {
                    for (String sstr : strs) {
                        String ss = sstr.substring(2);
                        sb.append(ss).append(",");
                    }
                    String ss = sb.toString();
                    prefix = ss.substring(0, ss.length() - 1);
                } else {
                    prefix = str.substring(0, str.length() - 1);
                }

                info.setPrefix(prefix);
            }
            if (Objects.nonNull(row.getCell(5))) {
                row.getCell(5).setCellType(CellType.STRING);
                final BigDecimal price = new BigDecimal(row.getCell(5).getStringCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            info.setCurrency("EUR");
            info.setAgentId(agentId);
            info.setType("mobile");
            smsPricingList.add(info);
        }
        return smsPricingList;
    }

    @Autowired
    private SmsUserRoleMapper smsUserRoleMapper;
    @Autowired
    private SmsRoleMapper smsRoleMapper;

    @Override
    public Result updateUserPrice(SmsUserAllowOperatorDTO smsUserAllowOperatorDTO) {

        //查询用户所属角色
        Integer roleLevel = getRole(smsUserAllowOperatorDTO.getOperatorUserId());
        StringBuffer sb = new StringBuffer();
        //销售角色批量修改同国家同运营商的售价
        if (roleLevel != null && roleLevel.equals(1)) {
            List<String> allOperatorIds = smsUserAllowOperatorDTO.getAllowOperatorIds();
            List<SmsUserAllowOperator> uaoList = new ArrayList<>();
            for (String allowOperatorId : allOperatorIds) {
                SmsUserAllowOperator userAllowOperator = this.getById(allowOperatorId);
                QueryWrapper<SmsUserAllowOperator> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("user_id", userAllowOperator.getUserId());
                queryWrapper.eq("country_code", userAllowOperator.getCountryCode());
                queryWrapper.eq("network_name", userAllowOperator.getNetworkName());
                List<SmsUserAllowOperator> qwList = this.list(queryWrapper);
                for (SmsUserAllowOperator uao : qwList) {
                    BigDecimal defaultPrice = uao.getDefaultPrice();
                    BigDecimal salesPrice = smsUserAllowOperatorDTO.getPrice();
                    if (salesPrice.divide(defaultPrice, 8, RoundingMode.HALF_DOWN).compareTo(new BigDecimal(uao.getDefaultAndSales())) == -1) {
                        sb.append(userAllowOperator.getCountryCode() + " and " + userAllowOperator.getNetworkName() + ",修改此售价,请联系管理员申请！");
                    } else {
                        uao.setPrice(smsUserAllowOperatorDTO.getPrice());
                        uaoList.add(uao);
                    }

                }
            }
            if (this.updateBatchById(uaoList)) {
                return ResultGenerator.genSuccessResult("保存或修改成功！" + sb);
            } else {
                return ResultGenerator.genClientFailResult("保存或修改失败！");
            }
        } else {
            //管理员正常修改价格
            List<SmsUserAllowOperator> suaoList = new ArrayList<>();
            List<String> allOperatorIds = smsUserAllowOperatorDTO.getAllowOperatorIds();
            if (allOperatorIds != null && allOperatorIds.size() > 0) {
                for (String allOperatorId : allOperatorIds) {
                    SmsUserAllowOperator smsUserAllowOperator = this.getById(allOperatorId);
                    //BeanUtils.copyProperties(smsUserAllowOperatorDTO, smsUserAllowOperator);
                    if (smsUserAllowOperatorDTO.getPrice() != null) {
                        smsUserAllowOperator.setPrice(smsUserAllowOperatorDTO.getPrice());
                    }
                    if (smsUserAllowOperatorDTO.getDefaultPrice() != null) {
                        smsUserAllowOperator.setDefaultPrice(smsUserAllowOperatorDTO.getDefaultPrice());
                    }
                    if (smsUserAllowOperatorDTO.getCostPrice() != null) {
                        smsUserAllowOperator.setCostPrice(smsUserAllowOperatorDTO.getCostPrice());
                    }
                    smsUserAllowOperator.setAllowOperatorId(allOperatorId);

                    suaoList.add(smsUserAllowOperator);
                }
            }
            if (this.updateBatchById(suaoList)) {
                return ResultGenerator.genSuccessResult("保存或修改成功！");
            } else {
                return ResultGenerator.genClientFailResult("保存或修改失败！");
            }
        }

    }


    @Override
    public boolean updateStatusByOperatorIds(List<String> operatorIds, Boolean status) {
        return lambdaUpdate()
                .set(SmsUserAllowOperator::getStatus, status)
                .in(SmsUserAllowOperator::getOperatorId, operatorIds)
                .update();
    }


    @Override
    public Result importUserPriceExcel(MultipartFile file, Integer userId) {
        List<SmsUserAllowOperator> infoList = null;
        try {
            infoList = readSmsInportPriceExcel(file.getInputStream(), userId);

        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        if (this.saveBatch(infoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }
    }

    private List<SmsUserAllowOperator> readSmsInportPriceExcel(InputStream inputStream, Integer userId) throws IOException, InvalidFormatException {
        List<SmsUserAllowOperator> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            SmsUserAllowOperator info = new SmsUserAllowOperator();
            info.setUserId(userId);
            info.setAllowOperatorId(UUID.randomUUID().toString().replaceAll("-", ""));
            if (Objects.nonNull(row.getCell(0))) {
                row.getCell(0).setCellType(CellType.STRING);
                info.setOperatorId(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                row.getCell(1).setCellType(CellType.STRING);
                info.setAgentId(row.getCell(1).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(2))) {
                row.getCell(2).setCellType(CellType.STRING);
                info.setCountryCode(row.getCell(2).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                info.setCountryName(row.getCell(3).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(4))) {
                row.getCell(4).setCellType(CellType.STRING);
                info.setCountryDisplayName(row.getCell(4).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(5))) {
                row.getCell(5).setCellType(CellType.STRING);
                info.setNetworkCode(row.getCell(5).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(6))) {
                row.getCell(6).setCellType(CellType.STRING);
                info.setNetworkAliases(row.getCell(6).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(7))) {
                row.getCell(7).setCellType(CellType.STRING);
                info.setNetworkName(row.getCell(7).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(8))) {
                row.getCell(8).setCellType(CellType.STRING);
                info.setPrefix(row.getCell(8).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(9))) {
                row.getCell(9).setCellType(CellType.STRING);
                if (StringUtils.isNotEmpty(row.getCell(9).getStringCellValue())) {
                    info.setCostPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(9).getStringCellValue())));
                }
            }
            if (Objects.nonNull(row.getCell(10))) {
                row.getCell(10).setCellType(CellType.STRING);
                if (StringUtils.isNotEmpty(row.getCell(10).getStringCellValue())) {
                    info.setPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(10).getStringCellValue())));
                }
            }
            if (Objects.nonNull(row.getCell(11))) {
                row.getCell(11).setCellType(CellType.STRING);
                if (StringUtils.isNotEmpty(row.getCell(10).getStringCellValue())) {
                    info.setDefaultPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(11).getStringCellValue())));
                }
            }

            if (Objects.nonNull(row.getCell(12))) {
                row.getCell(12).setCellType(CellType.STRING);
                info.setCurrency(row.getCell(12).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(13))) {
                row.getCell(13).setCellType(CellType.STRING);
                info.setType(row.getCell(13).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(14))) {
                row.getCell(14).setCellType(CellType.STRING);
                info.setMcc(row.getCell(14).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(15))) {
                row.getCell(15).setCellType(CellType.STRING);
                info.setMnc(row.getCell(15).getStringCellValue());
            }
            /*if (Objects.nonNull(row.getCell(16))) {
            	row.getCell(16).setCellType(CellType.STRING);
            	info.setUserId(Integer.valueOf(row.getCell(16).getStringCellValue()));
            }*/
            if (Objects.nonNull(row.getCell(17))) {
                row.getCell(17).setCellType(CellType.STRING);
                info.setAllowOperatorId(row.getCell(17).getStringCellValue());
            }
            if (StringUtils.isEmpty(info.getCountryCode())) {
                continue;
            }
            smsPricingList.add(info);
        }
        return smsPricingList;
    }

    @Autowired
    private SmsAgentMapper smsAgentMapper;

    @Override
    public boolean updateUserAllAllowOperator(SearchSmsPricingDTO searchSmsPricingDTO) {
        String agentId = null;
        if (searchSmsPricingDTO.getAgentAliasName() != null && StringUtils.isNotEmpty(searchSmsPricingDTO.getAgentAliasName())) {
            QueryWrapper<SmsAgent> wrapper = new QueryWrapper<>();
            wrapper.eq("agent_alias_name", searchSmsPricingDTO.getAgentAliasName());
            SmsAgent smsAgent = smsAgentMapper.selectOne(wrapper);
            agentId = smsAgent.getAgentId();
        } else if (searchSmsPricingDTO.getAgentName() != null && StringUtils.isNotEmpty(searchSmsPricingDTO.getAgentAliasName())) {
            QueryWrapper<SmsAgent> wrapper = new QueryWrapper<>();
            wrapper.eq("agent_name", searchSmsPricingDTO.getAgentName());
            SmsAgent smsAgent = smsAgentMapper.selectOne(wrapper);
            agentId = smsAgent.getAgentId();
        }

        return lambdaUpdate()
                .set(SmsUserAllowOperator::getStatus, searchSmsPricingDTO.getStatus())
                .eq(SmsUserAllowOperator::getUserId, searchSmsPricingDTO.getUserId())
                .eq(StringUtils.isNotEmpty(agentId), SmsUserAllowOperator::getAgentId, agentId)
                .eq(StringUtils.isNotEmpty(searchSmsPricingDTO.getCountryCode()), SmsUserAllowOperator::getCountryCode, searchSmsPricingDTO.getCountryCode())
                .eq(StringUtils.isNotEmpty(searchSmsPricingDTO.getCountryName()), SmsUserAllowOperator::getCountryName, searchSmsPricingDTO.getCountryName())
                .eq(StringUtils.isNotEmpty(searchSmsPricingDTO.getPrefix()), SmsUserAllowOperator::getPrefix, searchSmsPricingDTO.getPrefix())
                .eq(StringUtils.isNotEmpty(searchSmsPricingDTO.getNetworkName()), SmsUserAllowOperator::getNetworkName, searchSmsPricingDTO.getNetworkName())
                .eq(StringUtils.isNotEmpty(searchSmsPricingDTO.getCurrency()), SmsUserAllowOperator::getCurrency, searchSmsPricingDTO.getCurrency())
                .eq(StringUtils.isNotEmpty(searchSmsPricingDTO.getDefaultPrice()), SmsUserAllowOperator::getDefaultPrice, searchSmsPricingDTO.getDefaultPrice())
                .eq(StringUtils.isNotEmpty(searchSmsPricingDTO.getCostPrice()), SmsUserAllowOperator::getCostPrice, searchSmsPricingDTO.getCostPrice())
                .eq(StringUtils.isNotEmpty(searchSmsPricingDTO.getPrice()), SmsUserAllowOperator::getPrice, searchSmsPricingDTO.getPrice())
                .update();

    }


    @Override
    public Result deleteUserPrice(SmsUpdatePricingDTO smsUpdatePricingDTO) {
        if (this.removeByIds(smsUpdatePricingDTO.getOperatorIds())) {
            return ResultGenerator.genSuccessResult("删除成功！");
        } else {
            return ResultGenerator.genServerFailResult("删除失败！请联系管理员");
        }

    }


    @Override
    public Result updateSpecialOfferStatus(SmsUserAllowOperatorDTO smsUserAllowOperatorDTO) {
        /*Integer roleLevel = getRole(smsUserAllowOperatorDTO.getOperatorUserId());
        if (roleLevel == 1) {
            SmsUserAllowOperator userAllowOperator = this.getById(smsUserAllowOperatorDTO.getAllowOperatorId());
            QueryWrapper<SmsUserAllowOperator> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("user_id", userAllowOperator.getUserId());
            queryWrapper.eq("country_code", userAllowOperator.getCountryCode());
            queryWrapper.eq("network_name", userAllowOperator.getNetworkName());
            List<SmsUserAllowOperator> qwList = this.list(queryWrapper);
            for (SmsUserAllowOperator uao : qwList) {
                uao.setSpecialOfferStatus(smsUserAllowOperatorDTO.getSpecialOfferStatus());
            }
            if (this.updateBatchById(qwList)) {
                return ResultGenerator.genSuccessResult("修改成功！");
            } else {
                return ResultGenerator.genServerFailResult("修改失败！请联系管理员");
            }
        } else {*/
        return ResultGenerator.genServerFailResult("修改失败！只有销售角色才能申请！");
        //}

    }


    @Override
    public boolean updateStatusByAgentIds(SmsAgentDTO smsAgentDTO) {

        return this.lambdaUpdate()
                .set(SmsUserAllowOperator::getStatus, smsAgentDTO.getStatus())
                .in(SmsUserAllowOperator::getAgentId, smsAgentDTO.getAgentIds())
                .update();

    }


    @Override
    public List<SmsUserAllowOperator> queryUserAllowOperatorByParentId(Integer parentId, QueryPricingByConditionsDTO conditionsDTO) {
        return lambdaQuery()
                .eq(StringUtils.isNotEmpty(conditionsDTO.getCountryCode()), SmsUserAllowOperator::getCountryCode, conditionsDTO.getCountryCode())
                .eq(StringUtils.isNotEmpty(conditionsDTO.getCountryName()), SmsUserAllowOperator::getCountryName, conditionsDTO.getCountryName())
                .eq(StringUtils.isNotEmpty(conditionsDTO.getNetworkCode()), SmsUserAllowOperator::getNetworkCode, conditionsDTO.getNetworkCode())
                .eq(StringUtils.isNotEmpty(conditionsDTO.getNetworkName()), SmsUserAllowOperator::getNetworkName, conditionsDTO.getNetworkName())
                .eq(StringUtils.isNotEmpty(conditionsDTO.getPrefix()), SmsUserAllowOperator::getPrefix, conditionsDTO.getPrefix())
                .eq(SmsUserAllowOperator::getUserId, parentId)
                .in(CollectionUtils.isNotEmpty(conditionsDTO.getOperatorIds()), SmsUserAllowOperator::getOperatorId, conditionsDTO.getOperatorIds())
                .orderByAsc(SmsUserAllowOperator::getCountryCode)
                .list();
    }


    @Override
    public Result updatePriceRelation(SmsUserAllowOperatorDTO smsUserAllowOperatorDTO) {
        //查询用户所属角色
        Integer roleLevel = getRole(smsUserAllowOperatorDTO.getOperatorUserId());
        //销售角色批量修改同国家同运营商的售价
        if (roleLevel != null && roleLevel.equals(1)) {
            SmsUserAllowOperator userAllowOperator = this.getById(smsUserAllowOperatorDTO.getAllowOperatorId());
            QueryWrapper<SmsUserAllowOperator> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("user_id", userAllowOperator.getUserId());
            queryWrapper.eq("country_code", userAllowOperator.getCountryCode());
            queryWrapper.eq("network_name", userAllowOperator.getNetworkName());
            List<SmsUserAllowOperator> qwList = this.list(queryWrapper);
            for (SmsUserAllowOperator uao : qwList) {
                BigDecimal costPrice = uao.getCostPrice();
                BigDecimal defaultPrice = uao.getDefaultPrice();
                BigDecimal salesPrice = smsUserAllowOperatorDTO.getPrice();

                uao.setPrice(smsUserAllowOperatorDTO.getPrice());
            }

            if (this.updateBatchById(qwList)) {
                return ResultGenerator.genSuccessResult("保存或修改成功！");
            } else {
                return ResultGenerator.genClientFailResult("保存或修改失败！");
            }
        } else {
            //管理员修改价格控制
            List<SmsUserAllowOperator> suaoList = new ArrayList<>();
            List<String> allOperatorIds = smsUserAllowOperatorDTO.getAllowOperatorIds();

            if (allOperatorIds != null && allOperatorIds.size() > 0) {
                for (String allOperatorId : allOperatorIds) {
                    SmsUserAllowOperator smsUserAllowOperator = this.getById(allOperatorId);
                    smsUserAllowOperator.setCostAndDefault(smsUserAllowOperatorDTO.getCostAndDefault());
                    smsUserAllowOperator.setDefaultAndSales(smsUserAllowOperatorDTO.getDefaultAndSales());

                    suaoList.add(smsUserAllowOperator);
                }
            }

            if (this.updateBatchById(suaoList)) {
                return ResultGenerator.genSuccessResult("保存或修改成功！");
            } else {
                return ResultGenerator.genClientFailResult("保存或修改失败！");
            }
        }

    }

    @Override
    public Result updateUserAllPrice(SmsUserAllowOperatorDTO smsUserAllowOperatorDTO) {
        Integer roleLevel = getRole(smsUserAllowOperatorDTO.getOperatorUserId());
        String agentId = null;
        if (smsUserAllowOperatorDTO.getAgentAliasName() != null && StringUtils.isNotEmpty(smsUserAllowOperatorDTO.getAgentAliasName())) {
            QueryWrapper<SmsAgent> wrapper = new QueryWrapper<>();
            wrapper.eq("agent_alias_name", smsUserAllowOperatorDTO.getAgentAliasName());
            SmsAgent smsAgent = smsAgentMapper.selectOne(wrapper);
            agentId = smsAgent.getAgentId();
        } else if (smsUserAllowOperatorDTO.getAgentName() != null && StringUtils.isNotEmpty(smsUserAllowOperatorDTO.getAgentAliasName())) {
            QueryWrapper<SmsAgent> wrapper = new QueryWrapper<>();
            wrapper.eq("agent_name", smsUserAllowOperatorDTO.getAgentName());
            SmsAgent smsAgent = smsAgentMapper.selectOne(wrapper);
            agentId = smsAgent.getAgentId();
        }

        List<SmsUserAllowOperator> uaoList = lambdaQuery()
                .eq(SmsUserAllowOperator::getUserId, smsUserAllowOperatorDTO.getUserId())
                .eq(StringUtils.isNotEmpty(agentId), SmsUserAllowOperator::getAgentId, agentId)
                .eq(StringUtils.isNotEmpty(smsUserAllowOperatorDTO.getCountryCode()), SmsUserAllowOperator::getCountryCode, smsUserAllowOperatorDTO.getCountryCode())
                .eq(StringUtils.isNotEmpty(smsUserAllowOperatorDTO.getCountryName()), SmsUserAllowOperator::getCountryName, smsUserAllowOperatorDTO.getCountryName())
                .eq(StringUtils.isNotEmpty(smsUserAllowOperatorDTO.getPrefix()), SmsUserAllowOperator::getPrefix, smsUserAllowOperatorDTO.getPrefix())
                .eq(StringUtils.isNotEmpty(smsUserAllowOperatorDTO.getNetworkName()), SmsUserAllowOperator::getNetworkName, smsUserAllowOperatorDTO.getNetworkName())
                .eq(StringUtils.isNotEmpty(smsUserAllowOperatorDTO.getCurrency()), SmsUserAllowOperator::getCurrency, smsUserAllowOperatorDTO.getCurrency())
                .eq(ObjectUtils.isNotEmpty(smsUserAllowOperatorDTO.getDefaultPrice()), SmsUserAllowOperator::getDefaultPrice, smsUserAllowOperatorDTO.getDefaultPrice())
                .eq(ObjectUtils.isNotEmpty(smsUserAllowOperatorDTO.getCostPrice()), SmsUserAllowOperator::getCostPrice, smsUserAllowOperatorDTO.getCostPrice())
                .eq(ObjectUtils.isNotEmpty(smsUserAllowOperatorDTO.getPrice()), SmsUserAllowOperator::getPrice, smsUserAllowOperatorDTO.getPrice())
                .list();


        //管理员正常修改价格

        List<SmsUserAllowOperator> suaoList = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        if (uaoList != null && uaoList.size() > 0) {
            for (SmsUserAllowOperator smsUserAllowOperator : uaoList) {
                BigDecimal salesPrice = smsUserAllowOperatorDTO.getPrice2();
                BigDecimal defaultPrice = smsUserAllowOperator.getDefaultPrice();
                if (salesPrice != null) {
                    smsUserAllowOperator.setPrice(salesPrice);
                }
                if (smsUserAllowOperatorDTO.getDefaultPrice2() != null) {
                    smsUserAllowOperator.setDefaultPrice(smsUserAllowOperatorDTO.getDefaultPrice2());
                }
                if (smsUserAllowOperatorDTO.getCostPrice2() != null) {
                    smsUserAllowOperator.setCostPrice(smsUserAllowOperatorDTO.getCostPrice2());
                }
                if (roleLevel != null && roleLevel.equals(1) && salesPrice != null && defaultPrice != null && salesPrice.divide(defaultPrice, 8, RoundingMode.HALF_DOWN).compareTo(new BigDecimal(smsUserAllowOperator.getDefaultAndSales())) == -1) {
                    sb.append(smsUserAllowOperator.getCountryCode() + " and " + smsUserAllowOperator.getNetworkName() + ",修改此售价,请联系管理员申请！");
                } else {
                    suaoList.add(smsUserAllowOperator);
                }

            }
        }

        if (suaoList.size() > 0 && this.updateBatchById(suaoList)) {
            return ResultGenerator.genSuccessResult("保存或修改成功！" + sb);
        } else {
            return ResultGenerator.genClientFailResult("保存或修改失败！" + sb);
        }

    }

    public boolean updateDisplayCurrencyById(Integer userId, String currency) {
        return lambdaUpdate()
                .set(SmsUserAllowOperator::getCurrency, currency)
                .eq(SmsUserAllowOperator::getUserId, userId)
                .update();
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public boolean updateUserAllowOperatorFor(UpdatePriceByCountryDTO priceByCountryDTO) {
        return smsUserAllowOperatorMapper.updateCostPriceAndDefaultPrice(priceByCountryDTO);

    }
}
